/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBLag;

import ModelLag.Customer;
import ModelLag.LockerReservation;
import ModelLag.OrderArr;
import java.util.ArrayList;
import java.sql.*;
import java.util.GregorianCalendar;



/**
 *
 * @author Alex Ly
 */
public class DbLockerReservation {
    
    private Connection con;
    PreparedStatement pStmtSelect;
    
    public DbLockerReservation()
    {
        con = DbConnection1.getInstance().getDBcon();
    }
    
    public LockerReservation findLockerReservation(String phoneNo, boolean retriveAssociation)
    {
        if(pStmtSelect == null)
        {
            pStmtSelect = getPreparedStmt();
        }    
        LockerReservation lockerReservationObj = null;
        ResultSet results;
        
        try
        {
            pStmtSelect.setString(1, phoneNo);
            results = pStmtSelect.executeQuery();
            if(results.next())
            {
                lockerReservationObj = buildLockerReservation(results);
            }
        }
        catch(SQLException sqlE)
        {
          System.out.println(sqlE.getMessage());
            System.out.println("SQL Error");
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        
        return lockerReservationObj; 
    }
    
    public ArrayList<LockerReservation> getAllLockerReservations()
    {
        ArrayList<LockerReservation> list = new ArrayList<LockerReservation>();
        PreparedStatement pstmt = null;
        
        String allLockerReservations = "SELECT * FROM LockerReservation";
        
        System.out.println(allLockerReservations);
        
        LockerReservation lockerReservationObj = null;
        ResultSet results;
        
        try
        {
            pstmt = con.prepareStatement(allLockerReservations);
            pstmt.setQueryTimeout(5);
            results = pstmt.executeQuery();
            while(results.next())
            {
               lockerReservationObj = buildLockerReservation(results);
               list.add(lockerReservationObj);
            }
            pstmt.close();
        }
        catch(SQLException sqlE)
        {
            System.out.println(sqlE.getMessage());
            System.out.println("SQL Error");
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        
        return list;
    }
    
    public int insertLockerReservation(LockerReservation lockerReservationObj, boolean retriveAssociation)
    {
        int rc = -1;
        PreparedStatement pstmt = null;
        String query = null;
        try
        {   
            query = "INSERT INTO LockerReservation VALUES (?,?,?)"; 
            pstmt = con.prepareStatement(query);
            pstmt.setQueryTimeout(5);
            java.sql.Date tempDate = new java.sql.Date(lockerReservationObj.getDate().getTimeInMillis());
            pstmt.setDate(1, tempDate);
            pstmt.setString(2, lockerReservationObj.getCustomer().getPhoneNo());
            pstmt.setString(3, lockerReservationObj.getLockerType());
            
            pstmt.close();
        }
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error");
            System.out.println(sqlE.getMessage());

        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());

        }
        return rc;
    }
    
//    public int deleteLockerReservation(String phoneNo)
//    {
//        int rc = -1;
//        PreparedStatement pstmt = null;;
//        String query = null;
//        try
//        {
//            query = "DELETE FROM LockerReservation WHERE phoneNo";
//            pstmt = con.prepareStatement(query);
//            pstmt.setString(1, );
//            rc = pstmt.executeUpdate();
//            
//            pstmt.close();
//        }
//        catch(SQLException sqlE)
//        {
//            System.out.println("SQL Error");
//            System.out.println(sqlE.getMessage()); 
//        }
//        catch(Exception e)
//        {
//            e.getMessage();
//        }
//        return rc;
//    }
    
    private LockerReservation buildLockerReservation(ResultSet results)
    {
        LockerReservation lockerReservationObj = new LockerReservation();
        DbCustomer tempCustomer = new DbCustomer();
        GregorianCalendar calendar = new GregorianCalendar();

        try
        {
            calendar.setTimeInMillis(results.getDate("date").getTime());
            lockerReservationObj.setDate(calendar);
            lockerReservationObj.setLockerType(results.getString("lockerTpe"));
            lockerReservationObj.setCustomer(tempCustomer.findCustomer(results.getString("phoneNo")));
        }
        catch(Exception e)
         {
             System.out.println("error building LockerReservation object: " + e.getMessage());
         }
        return lockerReservationObj;
    }
    
    private PreparedStatement getPreparedStmt()
    {
        try
        {
            String query = "SELECT reservationID, date, lockerType, phoneNo "
                         + "FROM Locker "
                         + "WHERE phoneNo = ?";
            System.out.println("Preparestatement " + query);
            pStmtSelect = con.prepareStatement(query);
            pStmtSelect.setQueryTimeout(5);
        }
        catch(SQLException sqlE)
        {
            System.out.println(sqlE.getMessage());
            System.out.println("SQL Error");
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
        return pStmtSelect;
    }
}
